package com.aptech.business;

import com.aptech.dbaccess.DBConnection;
import com.aptech.dto.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CustomerBusiness {

    private static String DBNAME;
    private static Connection conn;

    public static List getCustomerList(String name) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        getConnection();
        if (name == null) name = "";
        String query = "SELECT custid, custName FROM [" + DBNAME + "].[dbo].[Customer] WHERE custname LIKE ? ORDER BY custID";
        PreparedStatement st = conn.prepareStatement(query);
        st.setString(1, "%" + name + "%");
        ResultSet rs = st.executeQuery();
        List list = new ArrayList();

        while (rs.next()) {
            Customer cust = new Customer();
            cust.setCustId(rs.getString("custid"));
            cust.setCustName(rs.getString("custName"));

            list.add(cust);
        }
        if (list.size() == 0) {
            list = null;
        }
        return list;
    }

    public static void updateCustomer(Customer cust) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        String query = "UPDATE [" + DBNAME + "].[dbo].[Customer] SET custname = ?, description = ? WHERE custid = ?";
        getConnection();
        PreparedStatement st = conn.prepareStatement(query);
        st.setString(1, cust.getCustName());
        st.setString(2, cust.getDescription());
        st.setString(3, cust.getCustId());

        st.executeUpdate();
    }

    public static void insertCustomer(Customer cust) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO [" + DBNAME + "].[dbo].[Customer] ");
        sql.append("(custid, custname, description)");
        sql.append(" VALUES (?,?,?)");

        PreparedStatement ps = conn.prepareStatement(sql.toString());
        int i = 0;
        ps.setString(++i, cust.getCustId());
        ps.setString(++i, cust.getCustName());
        ps.setString(++i, cust.getDescription());

        ps.executeUpdate();
    }

    public static int checkExistence(String custId) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        getConnection();
        PreparedStatement st = conn.prepareStatement("SELECT COUNT(*) count FROM [" + DBNAME + "].[dbo].[Customer] WHERE custId = ?");

        st.setString(1, custId);

        int count = 0;
        ResultSet rs = st.executeQuery();
        if (rs.next()) {
            count = rs.getInt("count");
        }

        return count;
    }

    public static Customer getCustomerByName(String id) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        getConnection();
        String query = "SELECT custId, custName, description FROM [" + DBNAME + "].[dbo].[Customer] WHERE custId = ?";
        
        PreparedStatement st = conn.prepareStatement(query);
        st.setString(1, id);
        ResultSet rs = st.executeQuery();
        Customer cust = null;
        if (rs.next()) {
            cust = new Customer();
            cust.setCustId(rs.getString("custId"));
            cust.setCustName(rs.getString("custName"));
            cust.setDescription(rs.getString("description"));
        }
        return cust;
    }

     public static String deleteCustomers(String[] ids) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        getConnection();
        StringBuffer deleteItems = new StringBuffer();
        String query = "DELETE FROM [" + DBNAME + "].[dbo].[Customer] WHERE custId = ?";

        for (int i = 0; i < ids.length; i++) {
            if (checkExistence(ids[i]) != 0) {
                deleteItems.append(ids[i] + ",");
                PreparedStatement st = conn.prepareStatement(query);
                st.setString(1, ids[i]);
                st.executeUpdate();
            }
        }
        String result = null;
        if (deleteItems.length() > 0) {
            result = deleteItems.substring(0, deleteItems.length() - 1);
        }
        return result;
    }

    private static void getConnection() throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        DBNAME = DBConnection.getDBNAME();
        conn = DBConnection.getDBConnection();
    }

    public static void main(String[] a) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
       StringBuffer ab = new StringBuffer("1,2,3,");
        System.out.println(ab.substring(0, ab.length() - 1));
    }
}
